package com.zrf.excelExport.service;

import com.zrf.excelExport.mapper.SelectMapper;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * excel 导出service
 *
 * @author zrf
 *         Created by zrf on 2017/1/1.
 */
@Service
public class ExportService {
    /**
     * 日志
     */
    private final Logger logger = LoggerFactory.getLogger(getClass());
    /**
     * 查询mapper
     */
    @Resource
    private SelectMapper selectMapper;

    /**
     * excel 导出
     *
     * @param sqlFilePath  sql文件路径
     * @param sqlFileName  sql文件
     * @param outputStream 输出流
     */
    @Transactional(readOnly = true)
    public void exportExcel(String sqlFilePath, String sqlFileName, OutputStream outputStream) {
        try {
            //得到文件内容
            String sqlFullPath = sqlFilePath + "/" + sqlFileName;
            String path = FilenameUtils.getPath(sqlFullPath);
            String baseName = FilenameUtils.getBaseName(sqlFullPath);
            File file = new File(sqlFullPath);
            String sql = FileUtils.readFileToString(file);

            //从数据库获取数据
            List<Map<String, Object>> resultList = selectMapper.select(sql);

            //判断是否有excel模板,如果没有新建一个空的excel,否则的话走模板
            String excelTemplateFullPath = path + "/" + baseName + ".xlsx";
            File templateFile = new File(excelTemplateFullPath);
            boolean exists = templateFile.exists();
            boolean directory = templateFile.isDirectory();
            if (exists && !directory) {
                resultToTemplateExcel(resultList, outputStream, templateFile);
            } else {
                resultToNewExcel(resultList, outputStream);
            }
        } catch (IOException e) {
            logger.error("excel导出异常", e);
            try {
                outputStream.write(e.getMessage().getBytes());
            } catch (IOException e1) {
                logger.error("向输出流写入异常信息异常", e);
            }
        }

    }

    /**
     * 将数据库中文件写入到制定的模板里面并且导出
     *
     * @param resultList   数据库中数据
     * @param outputStream excel 输出流
     * @param templateFile 模板文件
     * @throws IOException 异常
     */
    private void resultToTemplateExcel(List<Map<String, Object>> resultList, OutputStream outputStream,
                                       File templateFile) throws IOException {
        InputStream inputStream = new FileInputStream(templateFile);
        Workbook workbook = null;
        String templateFileName = templateFile.getName();
        if ("xlsx".equals(templateFileName)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            workbook = new HSSFWorkbook(inputStream);
        }

        Sheet sheet = workbook.getSheetAt(0);
        //map各个key分别在第几列
        Row row0 = sheet.getRow(0);
        Iterator<Cell> cellIterator = row0.cellIterator();
        Map<String, Integer> keyIndexMap = new HashMap<>();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            keyIndexMap.put(cell.getStringCellValue(), cell.getColumnIndex());
        }

        //写入数据
        writeExcelBody(resultList, sheet, keyIndexMap);
        workbook.write(outputStream);
    }

    /**
     * 将数据库中数据返回到新的excel中
     *
     * @param resultList   数据库中查询的数据
     * @param outputStream excel 输出流
     * @throws IOException ioexception
     */
    private void resultToNewExcel(List<Map<String, Object>> resultList, OutputStream outputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        if (!resultList.isEmpty()) {
            //设置excel 表头
            Row row0 = sheet.createRow(0);
            Map<String, Object> result0 = resultList.get(0);
            int j = 0;
            Map<String, Integer> keyIndexMap = new HashMap<>();
            for (Map.Entry<String, Object> entry : result0.entrySet()) {
                String column = entry.getKey();
                Cell cell = row0.createCell(j);
                cell.setCellValue(column);
                keyIndexMap.put(column, j);
                j++;
            }
            //写入数据体
            writeExcelBody(resultList, sheet, keyIndexMap);
        }

        workbook.write(outputStream);
    }

    /**
     * 写excel 主体数据
     *
     * @param resultList  数据库返回结果
     * @param sheet       工作簿
     * @param keyIndexMap map各个key分别在第几列
     */
    private void writeExcelBody(List<Map<String, Object>> resultList, Sheet sheet, Map<String, Integer> keyIndexMap) {
        int i = 1;
        //设置excel数据
        for (Map<String, Object> resultItem : resultList) {
            Row row = sheet.createRow(i);
            for (Map.Entry<String, Object> entry : resultItem.entrySet()) {
                String column = entry.getKey();
                Integer cellIndex = keyIndexMap.get(column);
                if(cellIndex != null) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(entry.getValue().toString());
                }
            }
            i++;
        }
    }
}
